package cn.thirdplatform.utils;

import cn.thirdplatform.annotation.PoiName;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Assert;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

/**
 * excel 处理工具
 * 1.表头：以第一行每列的值构造成数组格式返回
 * 2.内容：以当前列第二行的名称为key,第三行为val的 HashMap,第四行即以后同理,返回ArrayList<HashMap>集合
 *
 * @author chenyong
 * @Date 2018-11-10 19:12
 */
@Slf4j
public class ExcelUtils {
    private static final Pattern p = Pattern.compile("^//d+(//.//d+)?$");
    private static final String DEFAULT_PATTERN = "yyyy-MM-dd hh:mm:ss";

    /**
     * 判断excel版本是否为2003
     */
    public static boolean isExcel2003(String fileName) {
        return fileName.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 判断excel版本是否为2007
     */
    public static boolean isExcel2007(String fileName) {
        return fileName.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 获取文件名（去掉后缀）
     */
    public static String getFileNameNoEx(String fileName) {
        if ((fileName != null) && (fileName.length() > 0)) {
            int dot = fileName.lastIndexOf('.');
            if ((dot > -1) && (dot < (fileName.length()))) {
                return fileName.substring(0, dot);
            }
        }
        return fileName;
    }

    /**
     * 获取Workbook对象(针对浏览器上传使用)
     */
    public static Workbook getWorkbook(MultipartFile file) {
        Workbook wb = null;
        String fileName = file.getOriginalFilename();
        FileInputStream fin = null;
        try {
            fin = (FileInputStream) file.getInputStream();
            if (isExcel2007(fileName)) {
                wb = new XSSFWorkbook(fin);
            } else {
                wb = new HSSFWorkbook(fin);
            }
        } catch (IOException e) {
            log.error("io 异常", e);
        } finally {
            try {
                fin.close();
            } catch (IOException e) {
                log.error("io 异常", e);
            }
        }
        return wb;
    }

    /**
     * 以File类型,获取Workbook对象(主要是针对测试使用)
     */
    public static Workbook getWorkbook(File file) {
        Workbook wb = null;
        String fileName = file.getName();
        FileInputStream fin = null;
        try {
            fin = new FileInputStream(file);
            if (isExcel2007(fileName)) {
                wb = new XSSFWorkbook(fin);
            } else {
                wb = new HSSFWorkbook(fin);
            }
        } catch (IOException e) {
            log.error("io 异常", e);
        } finally {
            try {
                fin.close();
            } catch (IOException e) {
                log.error("io 异常", e);
            }
        }
        return wb;
    }

    /**
     * 获取Excel第一行的名字
     *
     * @param wb
     * @return
     */
    public static ArrayList<String[]> getTitle(Workbook wb) {
        Assert.assertNotNull("Workbook is Empty", wb);
        //获取第一个sheet对象
        Sheet sheet = wb.getSheetAt(0);
        Assert.assertNotNull("Workbook is Empty", sheet);
        ArrayList<String[]> arrayList = new ArrayList<>();
        //获取第一行
        Row row = sheet.getRow(1);
        //获取列数
        short maxColumn = row.getLastCellNum();
        String[] cells = new String[maxColumn];
        for (int i = 0; i < maxColumn; i++) {
            cells[i] = getCellValue(row.getCell(i));
        }
        arrayList.add(cells);
        return arrayList;

    }

    /**
     * 获取Excel的内容,从第三行开始获取(需要注意第二行的为字段值)
     * Ex:
     * name  age sex
     * 张三   12  男
     * 李四   11  女
     * 最终结构
     * Array[0]
     * hashMap(name,张三)
     * hashMap(age,12)
     * hashMap(sex,男)
     * Array[1]
     * hashMap(name,李四)
     * hashMap(age,11)
     * hashMap(sex,女)
     *
     * @param wb
     * @return
     */
    public static ArrayList<HashMap> getContent(Workbook wb) {
        Assert.assertNotNull("Workbook is Empty", wb);
        //获取第一个sheet对象
        Sheet sheet = wb.getSheetAt(0);
        Assert.assertNotNull("Workbook is Empty", sheet);
        ArrayList<HashMap> arrayList = new ArrayList<>();
        //获取第后一行
        int maxRow = sheet.getLastRowNum();
        for (int i = 3; i <= maxRow; i++) {
            //获取第二行的数据对象(表头)
            Row fixRow = sheet.getRow(2);
            //获取每行对象
            Row row = sheet.getRow(i);
            //获取每行列数
            if (null == row) {
                continue;
            }
            //按照固定列对象来生成最大数组
            HashMap<String, Object> hashMap = new HashMap<>();
            short maxColumn = fixRow.getLastCellNum();
            String[] cells = new String[maxColumn];
            for (int j = 0; j < maxColumn; j++) {
                Cell cell = row.getCell(j);
                cells[j] = null == cell ? "" : getCellValue(cell);
                //key,cells
                hashMap.put(getCellValue(fixRow.getCell(j)), cells[j]);
            }
            arrayList.add(hashMap);
        }
        return arrayList;

    }

    private static String getCellValue(Cell cell) {
        String cellValue;
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellTypeEnum() == NUMERIC) {
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 根据输出文件类型导出(xls格式)
     *
     * @param dataset
     * @param outFilePath
     * @throws IOException
     */
    public static <T> void exportExcel(Collection<T> dataset, File outFilePath, Class<T> clazz) throws IOException {
        if (!outFilePath.exists() || !outFilePath.isFile()) {
            throw new FileNotFoundException("file not exist");
        }
        OutputStream out = new FileOutputStream(outFilePath);
        exportExcel(dataset, out, clazz);
    }

    /**
     * 根据输出文件类型导出(自定义标题、时间戳类型,xls格式)
     *
     * @param title
     * @param dataset
     * @param outFilePath
     * @param pattern
     * @throws IOException
     */
    public static <T> void exportExcel(String title, Collection<T> dataset, File outFilePath, String pattern, Class<T> clazz) throws IOException {
        if (!outFilePath.exists()) {
            throw new FileNotFoundException("file not exist");
        }
        OutputStream out = new FileOutputStream(outFilePath);
        exportExcel(title, dataset, out, StringUtils.isBlank(pattern) ? DEFAULT_PATTERN : pattern, clazz);
    }

    /**
     * 默认导出类型(默认sheet标题、默认时间戳类型、使用输出流,xls格式)
     * note: 因为使用FileOutputStream,你需要自己控制File的创建
     *
     * @param <T>
     * @param dataset
     * @param out
     * @param clazz
     * @throws IOException
     */
    public static <T> void exportExcel(Collection<T> dataset, OutputStream out, Class<T> clazz) throws IOException {
        export("sheet", dataset, out, DEFAULT_PATTERN, clazz);
    }

    /**
     * 自定义标题名称,自定义时间类型(xls格式)
     *
     * @param title
     * @param dataset
     * @param out
     * @param pattern
     * @param clazz
     * @throws IOException
     */
    public static <T> void exportExcel(String title, Collection<T> dataset,
                                       OutputStream out, String pattern, Class<T> clazz) throws IOException {
        export(title, dataset, out, pattern, clazz);
    }

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     *
     * @param title   表格标题名(sheet名称)
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date
     * @param out     与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"
     * @param clazz
     */
    private static <T> void export(String title,
                                   Collection<T> dataset, OutputStream out, String pattern, Class<T> clazz) throws IOException {
        try {
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet(title);
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth(15);
            generatorTitle(workbook, sheet, clazz);
            generatorContent(workbook, sheet, dataset, pattern);
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            out.close();
        }

    }

    /**
     * Excel导出时,生成的内容
     *
     * @param workbook
     * @param sheet
     * @param dataset
     * @param pattern
     * @param <T>
     */
    @SuppressWarnings("Duplicates")
    private static <T> void generatorContent(HSSFWorkbook workbook, HSSFSheet sheet, Collection<T> dataset, String pattern) {
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
        style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBold(false);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            HSSFRow row = sheet.createRow(index);
            T t = it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try {
                    String textValue = null;
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName
                    );
                    Object val = getMethod.invoke(t);
                    if (null == val) {
                        break;
                    }
                    //这里是核心业务处理,根据指定的值,转换成对应的值
                    if (val instanceof Boolean) {
                        textValue = ((Boolean) val) ? "男" : "女";
                    } else if (val instanceof Date) {
                        Date date = (Date) val;
                        textValue = DateUtils.format(date, pattern);
                    } else {
                        textValue = val.toString();
                    }
                    if (textValue != null) {
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(
                                    textValue);
                            HSSFFont font3 = workbook.createFont();
                            font3.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
                            richString.applyFont(font3);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException | NoSuchMethodException | IllegalArgumentException | IllegalAccessException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @SuppressWarnings("Duplicates")
    private static <T> void generatorTitle(HSSFWorkbook workbook, HSSFSheet sheet, Class<T> clazz) {
        // 生成一个样式(标题)
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式(标题)
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体(标题)
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式(标题)
        style.setFont(font);
        // 产生表格标题行(标题)
        HSSFRow row = sheet.createRow(0);
        //反射获取@POI注解上的名称
        Field[] declaredFields = clazz.getDeclaredFields();
        int i = 0;
        for (Field declaredField : declaredFields) {
            PoiName declaredAnnotation = declaredField.getDeclaredAnnotation(PoiName.class);
            if (declaredAnnotation != null) {
                HSSFCell cell = row.createCell(i++);
                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(declaredAnnotation.name());
                cell.setCellValue(text);
            }
        }
    }

    /**
     * 测试方法,自己定义一个JavaBean来替换Student类,使用默认的StudentPoi也可以
     */
    /*@Test
    public void t() {
        // 测试学生
        List<PoiStudent> dataset = Lists.newArrayList();
        dataset.add(new PoiStudent(10000001, "张三", 20, true, new Date()));
        dataset.add(new PoiStudent(20000002, "李四", 24, false, new Date()));
        dataset.add(new PoiStudent(30000003, "王五", 22, true, new Date()));
        // 测试图书
        try {
            File file = new File("D://baseDTO.xlsx");
            if (!file.exists()) {
                file.createNewFile();
            } else {
                file.delete();
                file.createNewFile();
            }
            OutputStream out = new FileOutputStream(file);
            ExcelUtils.exportExcel(dataset, out, PoiStudent.class);
            out.close();
            log.info("excel导出成功！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }*/
}
